﻿import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class StudentDao {

	public static ArrayList<Student> queryStudentList() {
		 ArrayList<Student> studentList = new ArrayList<>();
		/**
		 * 查询学生列表
		 */
			Connection conn = ConnectionUtil.getConnection();
	        Statement stmt = null;
	        try{
	           
	            // 执行查询
	            System.out.println(" 实例化Statement对象...");
	            stmt = conn.createStatement();
	            String sql;
	            sql = "SELECT id, name, age, bjmc, zy, sex FROM student join class "+ "on student.bjbh=class.bjbh";
	            ResultSet rs =  stmt.executeQuery(sql);
	        
	            // 展开结果集数据库
	            while(rs.next()){
	                // 通过字段检索
	                int id  = rs.getInt("id");
	                String name = rs.getString("name");
	                int age = rs.getInt("age");
	                String bjmc = rs.getString("bjmc"); 
	                String zy = rs.getString("zy");
	                int sex = rs.getInt("sex");
	                String sex1="男";
	                if(sex==1){
	                	sex1="男";
	                }else{
	                	sex1="女";
	                }
	                Student student = new Student(id, name, age, bjmc, zy, sex1);
	                studentList.add(student);
	            }
	            /*// 完成后关闭
	            rs.close();
	            stmt.close();*/
	        }catch(SQLException se){
	            // 处理 JDBC 错误
	            se.printStackTrace();
	        }catch(Exception e){
	            // 处理 Class.forName 错误
	            e.printStackTrace();
	        }/*finally{
	            // 关闭资源
	            try{
	                if(stmt!=null) stmt.close();
	            }catch(SQLException se2){
	            }// 什么都不做
	            try{
	                if(conn!=null) conn.close();
	            }catch(SQLException se){
	                se.printStackTrace();
	            }
	        }*/
	        
	        System.out.println("查询成功！");
	        return studentList;
		}

	public static void addUseStatement(Student student) {
		Connection connection=ConnectionUtil.getConnection();
		try {
			Statement statement = connection.createStatement();
			
			int sex1 = 1;
			if(student.getSex().equals("男") ) {
				sex1 = 1;
			}else {
				sex1 = 0;
			}
		 int bjbh=ClassDao.getBjbh(student.getBjmc());
		String sql="INSERT INTO student VALUES (" + student.getId() + ", '"+student.getName()+"', "+student.getAge()+", "+bjbh+ ", "+sex1 +");";
		System.err.println(sql);
		statement.execute(sql);
		}catch(SQLException e){
			e.printStackTrace();
		}
		
	}
	public static void addUsePrepareStatement(Student student){
		Connection conn=ConnectionUtil.getConnection();
		String sql="insert into student values(?,?,?,?,?)";
		try {
			java.sql.PreparedStatement psta = conn.prepareStatement(sql);
			psta.setInt(1, student.getId());
			psta.setString(2, student.getName());
			psta.setInt(3, student.getAge());

			int bjbh = ClassDao.getBjbh(student.getBjmc());
			psta.setInt(4, bjbh);
			
			int sex1 = 1;
			if(student.getSex().equals("男") ) {
				sex1 = 1;
			}else {
				sex1 = 0;
			}
			psta.setInt(5, sex1);
			
			int result = psta.executeUpdate();
			if(result > 0) {
				System.out.println("插入成功");
			}else {
				System.out.println("插入失败");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		/*try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}*/
		
	}

	public static void del(String name) {
		Connection conn = ConnectionUtil.getConnection();

		String sql = "delete from student where name = '"+name+"'";
		
		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if(result > 0) {
				System.out.println("删除成功");
			}else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public static void update(String name1, int column, String value) {
		Connection conn =ConnectionUtil .getConnection();
		String sql=null;
		switch(column){
		case 1:
			sql="update student Set name='"+value+"' where name='"+name1+"'";
			break;
		case 2:
			sql="update student Set age='"+value+"' where name='"+name1+"'";
			break;
		case 3:
			sql="update student Set sex='"+value+"' where name='"+name1+"'";
			break;
		default:
			break;
			
		}
		
		try {
			Statement stat = conn.createStatement();
			int result =stat.executeUpdate(sql);
			if(result>0){
				System.out.println("更新成功");
			}else{
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public static int getIdWithName(String name2) {
		Connection conn =ConnectionUtil.getConnection();
		String sql = "select id from student where name = '"+name2+"'";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			if(rs.next()) {
				int id = rs.getInt(1);
				return id;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 110;
	}

}
	
